package com.gack.business.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.gack.business.model.Department;
import com.gack.business.vo.DepartmentItemVO;
import com.gack.business.vo.TopDepartmentAndUserItemVO;

@Repository
public interface DepartmentRepository extends JpaRepository<Department, String>, JpaSpecificationExecutor<Department>{

	@Query("select new com.gack.business.vo.DepartmentItemVO(d.id, d.name, d.number, d.parentId, d.level)"
			+ " from Department d"
			+ " where d.enterpriseId=:enterpriseId"
			+ " and d.parentId='0'"
			+ " and d.status=1")
	List<DepartmentItemVO> findTopDepartmentItemVOByEnterpriseId(@Param("enterpriseId") String enterpriseId);//查询该公司下所有一级部门ItemVO(不包含已删除部门)
	
	@Query("select d.id"
			+ " from Department d"
			+ " where d.parentId=:departmentId"
			+ " and d.enterpriseId=:enterpriseId"
			+ " and d.status=1")
	List<String> findNextSubDepartmentIdByDepartmentId(@Param("enterpriseId") String enterpriseId, @Param("departmentId")String departmentId);//查询某公司下某部门的下一级子部门id(不包含已删除部门)
	
	@Query("select d.id"
			+ " from Department d"
			+ " where d.parentId=:departmentId"
			+ " and d.enterpriseId=:enterpriseId")
	List<String> findNextSubDepartmentIdIncludeDeletedByDepartmentId(@Param("enterpriseId") String enterpriseId, @Param("departmentId")String departmentId);//查询某公司下某部门的下一级子部门id(包含已删除部门)
	
	@Query("select new com.gack.business.vo.DepartmentItemVO(d.id, d.name, d.number, d.parentId, d.level)"
			+ " from Department d"
			+ " where d.parentId=:departmentId"
			+ " and d.status=1")
	List<DepartmentItemVO> findNextSubDepartmentItemVOByDepartmentId(@Param("departmentId") String departmentId);//查询该部门下一级部门ItemVO(不包含已删除部门)
	
	@Query("select count(d.id)"
			+ " from Department d"
			+ " where d.id=:departmentId"
			+ " and d.enterpriseId=:enterpriseId"
			+ " and d.status=1")
	int countByEnterpriseIdAndDepartmentId(@Param("enterpriseId") String enterpriseId, @Param("departmentId") String departmentId);//查询某公司下某部门的数量(用来检测某公司和某部门是否有关联)(不包含已删除部门)
	
	@Query("select d.parentId"
			+ " from Department d"
			+ " where d.id=:departmentId")
	String findParentDepartmentId(@Param("departmentId") String departmentId);//查询该部门的父部门id
	
	@Query("select new com.gack.business.vo.DepartmentItemVO(d.id, d.name, d.number, d.parentId, d.level)"
			+ " from Department d"
			+ " where d.enterpriseId=:enterpriseId"
			+ " and d.status=1")
	List<DepartmentItemVO> findAllDepartmentItemVOByEnterpriseId(@Param("enterpriseId") String enterpriseId);//查询某公司下所有部门ItemVO(不包含已删除部门)

	@Query("select count(d.id)"
			+ " from Department d"
			+ " where d.name=:departmentName"
			+ " and d.enterpriseId=:enterpriseId"
			+ " and d.parentId=(select d1.parentId"
								+ " from Department d1"
								+ " where d1.id=:departmentId)"
			+ " and d.id!=:departmentId"
			+ " and d.status=1")
	int countSameNameByDepartmentIdExcludeMe(@Param("enterpriseId") String enterpriseId, @Param("departmentId") String departmentId, @Param("departmentName") String departmentName);//查询在某部门的父部门下的子部门中,是否存在重名(不包含已删除部门)
	
	@Query("select count(d.id)"
			+ " from Department d"
			+ " where d.name=:departmentName"
			+ " and d.enterpriseId=:enterpriseId"
			+ " and d.parentId=:parentDepartmentId"
			+ " and d.status=1")
	int countSameNameByParentmentId(@Param("enterpriseId") String enterpriseId, @Param("parentDepartmentId") String parentDepartmentId, @Param("departmentName") String departmentName);//查询在某父部门下的子部门中,是否存在重名(不包含已删除部门)
	
	@Query("select new com.gack.business.vo.TopDepartmentAndUserItemVO(d.id, d.name)"
			+ " from Department d"
			+ " where d.enterpriseId=:enterpriseId"
			+ " and d.parentId='0'"
			+ " and d.status=1")
	List<TopDepartmentAndUserItemVO> findTopDepartmentAndUserItemVO(@Param("enterpriseId") String enterpriseId);//查询某公司下一级部门的id以及name(不包含已删除部门)
	
	@Query("select d.name"
			+ " from Department d"
			+ " where d.id=:departmentId")
	String findNameById(@Param("departmentId")String departmentId);//根据部门id获取部门名称
	
	@Query("select d.level"
			+ " from Department d"
			+ " where d.id=:departmentId")
	String findLevelById(@Param("departmentId")String departmentId);//根据部门id获取level
	
	@Query("select d.no"
			+ " from Department d"
			+ " where d.id=:departmentId")
	String findNoByDepartmentId(@Param("departmentId") String departmentId);//根据部门id获得编号
	
	@Query("select count(d.id)"
			+ " from Department d"
			+ " where d.parentId=:departmentId"
			+ " and d.enterpriseId=:enterpriseId")
	int countSubDepartmentByDepartmentIdIncludeDeleted(@Param("enterpriseId")String enterpriseId, @Param("departmentId") String departmentId);//根据部门id获得它下一级子部门的部门数量(包括已删除数量)
	
	@Modifying
	@Query("update Department d set d.status=0, deleteTime=:date where d.enterpriseId=:enterpriseId")
	int deleteDepartmentByEnterpriseId(@Param("enterpriseId") String enterpriseId, @Param("date") Date date);
	
	@Query("select enterpriseId from Department where id=:departmentId")
	String findEnterpriseIdByDepartmentId(@Param("departmentId") String departmentId);//根据部门id查询对应公司id

}